﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PizzaService.Model;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections.ObjectModel;
using PizzaService.Events;
using PizzaService.Entities;
using PizzaService.Properties;

namespace PizzaService.Connector
{
    class PizzaDAL
    {
        private string m_ConnectionString;

        public delegate void ExceptionEventHandler(object sender, ExceptionEvent e);

        public event ExceptionEventHandler ExceptionEv;

        protected void RaiseExceptionEvent(string exceptionText)
        {
            // Raise the event by using the () operator. 
            if (ExceptionEv != null)
                ExceptionEv(this, new ExceptionEvent(exceptionText));
        }

        #region properties

        private ObservableCollection<Kunde> m_Kunden;

        public ObservableCollection<Kunde> Kunden
        {
            get
            {
                if (m_Kunden == null)
                    generateKundenModel();

                return m_Kunden;
            }

            set
            {
                m_Kunden = value;
            }
        }

        private ObservableCollection<Speise> m_Speisen;

        public ObservableCollection<Speise> Speisen
        {
            get
            {
                if (m_Zutaten == null)
                    generateZutatenModel();

                if (m_Speisen == null)
                    generateSpeisenModel();

                return m_Speisen;
            }

            set
            {
                m_Speisen = value;
            }
        }

        private ObservableCollection<Zutat> m_Zutaten;

        public ObservableCollection<Zutat> Zutaten
        {
            get
            {
                if (m_Zutaten == null)
                    generateZutatenModel();

                return m_Zutaten;
            }

            set
            {
                m_Zutaten = value;
            }
        }

        private List<ExtraZutat> m_ExtraZutaten;

        public List<ExtraZutat> ExtraZutaten
        {
            get
            {
                if (m_ExtraZutaten == null)
                    generateExtraZutatenModel();

                return m_ExtraZutaten;
            }

            set
            {
                m_ExtraZutaten = value;
            }
        }

        private ObservableCollection<Bestellung> m_Bestellungen;

        public ObservableCollection<Bestellung> Bestellungen
        {
            get
            {
                if (m_Bestellungen == null)
                    generateBestellungModel();

                return m_Bestellungen;
            }

            set
            {
                m_Bestellungen = value;
            }
        }

        #endregion

        public PizzaDAL(string connectionString)
        {
            this.m_ConnectionString = connectionString;
        }

        /// <summary>
        /// Generate the Model for the customers from SQL
        /// </summary>
        /// <returns></returns>
        private ObservableCollection<Kunde> generateKundenModel()
        {
            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                connection.Open();

                using (MySqlCommand command = connection.CreateCommand())
                {

                    command.CommandText = "SELECT * FROM kunden ORDER BY Name ASC";

                    try
                    {
                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            Kunde kunde;

                            ObservableCollection<Kunde> kunden = new ObservableCollection<Kunde>();

                            while (reader.Read())
                            {
                                kunde = new Kunde();

                                kunde.ID = reader.GetInt32(0);
                                kunde.Name = reader.IsDBNull(1) ? null : reader.GetString(1);
                                kunde.Vorname = reader.IsDBNull(2) ? null : reader.GetString(2);
                                kunde.Telefonnummer = reader.IsDBNull(3) ? null : reader.GetString(3);
                                kunde.Mail = reader.IsDBNull(4) ? null : reader.GetString(4);
                                kunde.PLZ = reader.IsDBNull(5) ? null : reader.GetString(5);
                                kunde.Ort = reader.IsDBNull(6) ? null : reader.GetString(6);
                                kunde.Strasse = reader.IsDBNull(7) ? null :  reader.GetString(7);
                                kunde.Hausnummer = reader.IsDBNull(8) ? null : reader.GetString(8);

                                kunden.Add(kunde);
                            }

                            Kunden = kunden;

                            return kunden;
                        }
                    }
                    //we should give an error to the GUI
                    catch (Exception ex)
                    {
                        RaiseExceptionEvent(ex.Message);
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// Generate the Model of our "Speisen"
        /// </summary>
        /// <returns></returns>
        private void generateSpeisenModel()
        {
            List<SpeiseZutaten> speiseZutaten = generateSpeiseZutaten();

            ObservableCollection<Speise> speisen = new ObservableCollection<Speise>();

            try
            {
                using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
                {
                    using (MySqlCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT * FROM speisen";

                        connection.Open();

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            Speise speise;

                            while (reader.Read())
                            {
                                speise = new Speise();

                                speise.ID = reader.GetInt32(0);
                                speise.Name = reader.GetString(2);
                                speise.Preis_klein = reader.GetDecimal(3);
                                speise.Preis_groß = reader.GetDecimal(4);

                                //
                                // we use an enum in our model for the type
                                switch (reader.GetInt32(1))
                                {
                                    case 0:
                                        speise.Typ = Speise.SpeiseTyp.Salat;
                                        break;
                                    case 1:
                                        speise.Typ = Speise.SpeiseTyp.Pizza;
                                        break;
                                    case 2:
                                        speise.Typ = Speise.SpeiseTyp.Pasta;
                                        break;
                                    default:
                                        throw new ArgumentOutOfRangeException("Nur SpeiseTyp von 0-2 erlaubt");
                                }

                                speisen.Add(speise);
                            }
                        }
                    }
                }

                addZutatenToSpeisen(speisen, speiseZutaten);

                m_Speisen = speisen;
            }
            //we should give an error to the GUI
            catch (Exception ex)
            {
                RaiseExceptionEvent(ex.Message);
                throw;
            }
        }

        /// <summary>
        /// Here we assign the Zutaten to the Speisen
        /// </summary>
        /// <param name="speisen"></param>
        /// <param name="speiseZutaten"></param>
        private void addZutatenToSpeisen(ObservableCollection<Speise> speisen, List<SpeiseZutaten> speiseZutaten)
        {
            foreach (var speiseZutat in speiseZutaten)
            {
                foreach (var speise in speisen)
                {
                    if (speise.ID == speiseZutat.SpeiseID)
                    {
                        //
                        // Initiate the list if it is empty
                        if (speise.Zutaten == null)
                        {
                            speise.Zutaten = new ObservableCollection<Zutat>();
                        }
                        foreach (var zutat in m_Zutaten)
                        {
                            if (zutat.ID == speiseZutat.ZutatID)
                                speise.Zutaten.Add(zutat);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Zutaten-Model erstellen
        /// </summary>
        private void generateZutatenModel()
        {
            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM zutaten";

                    connection.Open();

                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        try
                        {
                            ObservableCollection<Zutat> zutaten = new ObservableCollection<Zutat>();

                            while (reader.Read())
                            {
                                zutaten.Add(new Zutat { ID = reader.GetInt32(0), Name = reader.GetString(1) });
                            }

                            m_Zutaten = zutaten;
                        }
                        //we should give an error to the GUI
                        catch (Exception ex)
                        {
                            RaiseExceptionEvent(ex.Message);
                            throw;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Speisen-Zutaten erstellen
        /// </summary>
        /// <returns></returns>
        private List<SpeiseZutaten> generateSpeiseZutaten()
        {
            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM speisen_zutaten";

                    try
                    {
                        connection.Open();

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {

                            List<SpeiseZutaten> speisen = new List<SpeiseZutaten>();

                            while (reader.Read())
                            {
                                speisen.Add(new SpeiseZutaten { SpeiseID = reader.GetInt32(0), ZutatID = reader.GetInt32(1) });
                            }

                            return speisen;
                        }


                    }
                    //we should give an error to the GUI
                    catch (Exception ex)
                    {
                        RaiseExceptionEvent(ex.Message);
                        throw;
                    }
                }
            }
        }
        /// <summary>
        /// ExtraZutaten-Model erstellen
        /// </summary>
        private void generateExtraZutatenModel()
        {
            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM extra_zutaten";

                    try
                    {
                        connection.Open();

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {

                            List<ExtraZutat> extra_zutaten = new List<ExtraZutat>();

                            while (reader.Read())
                            {
                                extra_zutaten.Add(new ExtraZutat { ID = reader.GetInt32(0), Name = reader.GetString(1), Preis = reader.GetDecimal(2) });
                            }

                            m_ExtraZutaten = extra_zutaten;
                        }
                    }
                    //we should give an error to the GUI
                    catch (Exception ex)
                    {
                        RaiseExceptionEvent(ex.Message);
                        throw;
                    }
                }
            }
        }
        /// <summary>
        /// Kunde in der Datenbank updaten
        /// DB-Verbindung herstellen
        /// </summary>
        /// <param name="oldCustomer"></param>
        /// <param name="newCustomer"></param>
        /// <returns></returns>
        internal bool UpdateKunde(Kunde oldCustomer, Kunde newCustomer)
        {
            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {

                    command.Parameters.AddWithValue("@id", newCustomer.ID);

                    MySqlTransaction myTrans = null;

                    try
                    {
                        connection.Open();

                        // Start a local transaction
                        myTrans = connection.BeginTransaction();
                        // Must assign both transaction object and connection
                        // to Command object for a pending local transaction
                        command.Connection = connection;
                        command.Transaction = myTrans;

                        if (oldCustomer.Name != newCustomer.Name)
                        {
                            command.CommandText = "UPDATE kunden SET Name=@NameValue WHERE ID_KUNDE = @id";
                            command.Parameters.AddWithValue("@NameValue", newCustomer.Name);

                            command.ExecuteNonQuery();
                        }

                        if (oldCustomer.Vorname != newCustomer.Vorname)
                        {
                            command.CommandText = "UPDATE kunden SET Vorname = @VornameValue WHERE ID_KUNDE = @id";

                            command.Parameters.Add("@VornameValue", MySqlDbType.VarChar).Value = newCustomer.Vorname;

                            command.ExecuteNonQuery();
                        }

                        if (oldCustomer.Strasse != newCustomer.Strasse)
                        {
                            command.CommandText = "UPDATE kunden SET Straße = @Straße WHERE ID_KUNDE = @id";

                            command.Parameters.Add("@Straße", MySqlDbType.VarChar).Value = newCustomer.Strasse;

                            command.ExecuteNonQuery();
                        }

                        if (oldCustomer.Hausnummer != newCustomer.Hausnummer)
                        {
                            command.CommandText = "UPDATE kunden SET Hausnummer = @Hausnummer WHERE ID_KUNDE = @id";

                            command.Parameters.Add("@Hausnummer", MySqlDbType.VarChar).Value = newCustomer.Hausnummer;

                            command.ExecuteNonQuery();
                        }

                        if (oldCustomer.Ort != newCustomer.Ort)
                        {
                            command.CommandText = "UPDATE kunden SET Ort = @Ort WHERE ID_KUNDE = @id";

                            command.Parameters.Add("@Ort", MySqlDbType.VarChar).Value = newCustomer.Ort;

                            command.ExecuteNonQuery();
                        }

                        if (oldCustomer.PLZ != newCustomer.PLZ)
                        {
                            command.CommandText = "UPDATE kunden SET PLZ = @PLZ WHERE ID_KUNDE = @id";

                            command.Parameters.Add("@PLZ", MySqlDbType.VarChar).Value = newCustomer.PLZ;

                            command.ExecuteNonQuery();
                        }

                        if (oldCustomer.Telefonnummer != newCustomer.Telefonnummer)
                        {
                            command.CommandText = "UPDATE kunden SET Telefonnummer = @Telefonnummer WHERE ID_KUNDE = @id";

                            command.Parameters.Add("@Telefonnummer", MySqlDbType.VarChar).Value = newCustomer.Telefonnummer;

                            command.ExecuteNonQuery();
                        }

                        if (oldCustomer.Mail != newCustomer.Mail)
                        {
                            command.CommandText = "UPDATE kunden SET Mail = @Mail WHERE ID_KUNDE = @id";

                            command.Parameters.Add("@Mail", MySqlDbType.VarChar).Value = newCustomer.Mail;

                            command.ExecuteNonQuery();
                        }

                        myTrans.Commit();
                    }
                    catch (Exception)
                    {
                        // exception
                        RaiseExceptionEvent("Could not Update Customer, Trying Rollback");

                        try
                        {
                            myTrans.Rollback();
                        }
                        catch (MySqlException exx)
                        {
                            if (myTrans.Connection != null)
                            {
                                RaiseExceptionEvent("An exception of type " + exx.GetType() +
                                                   " was encountered while attempting to roll back the transaction.");
                            }
                        }

                        // Update Operation failed
                        return false;
                    }
                }
            }

            // Update Operation succeeded
            return true;
        }
        /// <summary>
        /// Neuen Kunde erstellen
        /// In DB eintragen
        /// </summary>
        /// <param name="newkunde"></param>
        /// <returns></returns>
        internal long CreateKunde(Kunde newkunde)
        {
            long id_Kunde = -1;

            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {
                    command.Parameters.AddWithValue("@Name", newkunde.Name);
                    command.Parameters.AddWithValue("@Vorname", newkunde.Vorname);
                    command.Parameters.AddWithValue("@Telefonnummer", newkunde.Telefonnummer);
                    command.Parameters.AddWithValue("@Mail", newkunde.Mail);
                    command.Parameters.AddWithValue("@PLZ", newkunde.PLZ);
                    command.Parameters.AddWithValue("@Ort", newkunde.Ort);
                    command.Parameters.AddWithValue("@Strasse", newkunde.Strasse);
                    command.Parameters.AddWithValue("@Hausnummer", newkunde.Hausnummer);

                    MySqlTransaction myTrans = null;


                    try
                    {
                        connection.Open();

                        // Start a local transaction
                        myTrans = connection.BeginTransaction();
                        // Must assign both transaction object and connection
                        // to Command object for a pending local transaction
                        command.Connection = connection;
                        command.Transaction = myTrans;

                        command.CommandText = @"INSERT INTO `kunden` (`ID_KUNDE`, `Name`, `Vorname`, `Telefonnummer`, `Mail`, `PLZ`, `Ort`, `Strasse`, `Hausnummer`) 
                VALUES (NULL, @Name, @Vorname, @Telefonnummer, @Mail, @PLZ, @Ort, @Strasse, @Hausnummer);";

                        command.ExecuteNonQuery();

                        command.CommandText = "SELECT LAST_INSERT_ID()";

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {

                            if (reader != null && reader.Read())
                                id_Kunde = reader.GetInt64(0);
                        }

                        myTrans.Commit();
                    }
                    catch (Exception)
                    {
                        // exception
                        RaiseExceptionEvent("Could not Create Customer, Trying Rollback");

                        try
                        {
                            myTrans.Rollback();
                        }
                        catch (MySqlException exx)
                        {
                            if (myTrans.Connection != null)
                            {
                                RaiseExceptionEvent("An exception of type " + exx.GetType() +
                                                   " was encountered while attempting to roll back the transaction.");
                            }
                        }

                        return -1;
                    }
                }
            }

            return id_Kunde;
        }

        /// <summary>
        /// Delete Customer from database
        /// </summary>
        /// <param name="kunde"></param>
        /// <returns></returns>
        internal bool DeleteKunde(Kunde kunde)
        {
            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {

                    command.Parameters.AddWithValue("@id", kunde.ID);

                    MySqlTransaction myTrans = null;


                    try
                    {
                        connection.Open();

                        // Start a local transaction
                        myTrans = connection.BeginTransaction();
                        // Must assign both transaction object and connection
                        // to Command object for a pending local transaction
                        command.Connection = connection;
                        command.Transaction = myTrans;

                        command.CommandText = "DELETE from kunden WHERE ID_KUNDE = @id";

                        command.ExecuteNonQuery();

                        myTrans.Commit();
                    }
                    catch (Exception ex)
                    {
                        if (ex.Message.Contains("foreign"))
                            RaiseExceptionEvent("Kunde kann nicht gelöscht werden, da Bestellungen getätigt wurden.");
                        else
                        // exception
                        RaiseExceptionEvent("Could not Delete Customer, Trying Rollback");

                        try
                        {
                            myTrans.Rollback();
                        }
                        catch (MySqlException exx)
                        {
                            if (myTrans.Connection != null)
                            {
                                RaiseExceptionEvent("An exception of type " + exx.GetType() +
                                                   " was encountered while attempting to roll back the transaction.");
                            }
                        }

                        return false;
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// Create a new Order, we have to create the food in another method
        /// </summary>
        /// <param name="newOrder"></param>
        /// <returns></returns>
        internal long CreateNewOrder(Bestellung newOrder)
        {
            long id_Bestellung = -1;

            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {

                    //command.Parameters.AddWithValue("@id", newkunde.ID);
                    command.Parameters.AddWithValue("@Datum", newOrder.Date);
                    command.Parameters.AddWithValue("@Lieferpauschale", newOrder.Lieferpauschale);
                    command.Parameters.AddWithValue("@Summe", newOrder.Summe);
                    command.Parameters.AddWithValue("@ID_Kunde", newOrder.Kunde.ID);
                    command.Parameters.AddWithValue("@State", (int)newOrder.Status);

                    MySqlTransaction myTrans = null;


                    try
                    {
                        connection.Open();

                        // Start a local transaction
                        myTrans = connection.BeginTransaction();
                        // Must assign both transaction object and connection
                        // to Command object for a pending local transaction
                        command.Connection = connection;
                        command.Transaction = myTrans;

                        command.CommandText = @"INSERT INTO `bestellungen` (`ID_BESTELLUNG`, `Datum`, `Lieferpauschale`, `Summe`, `Status`, `ID_Kunde`) 
                VALUES (NULL, @Datum, @Lieferpauschale, @Summe, @State, @ID_Kunde);";

                        command.ExecuteNonQuery();

                        //
                        // get the Last Inserted ID and to pass it to the next method which need it
                        command.CommandText = "SELECT LAST_INSERT_ID()";

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {

                            if (reader != null && reader.Read())
                                id_Bestellung = reader.GetInt64(0);
                        }
                        myTrans.Commit();
                    }
                    catch (Exception ex)
                    {
                        // exception
                        RaiseExceptionEvent("Could not Create Order, Trying Rollback");

                        try
                        {
                            myTrans.Rollback();
                        }
                        catch (MySqlException exx)
                        {
                            if (myTrans.Connection != null)
                            {
                                RaiseExceptionEvent("An exception of type " + exx.GetType() +
                                                   " was encountered while attempting to roll back the transaction.");
                            }
                        }

                        return -1;
                    }
                }
            }

            return createKundenSpeise(id_Bestellung, newOrder.Speisen);
        }

        /// <summary>
        /// Create the Food created by the Customers
        /// </summary>
        /// <param name="id_Bestellung"></param>
        /// <param name="lstSpeisen"></param>
        /// <returns></returns>
        private long createKundenSpeise(long id_Bestellung, ObservableCollection<Speise> lstSpeisen)
        {
            long id_KundenSpeise = -1;

            foreach (var item in lstSpeisen)
            {
                using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
                {
                    using (MySqlCommand command = connection.CreateCommand())
                    {

                        //command.Parameters.AddWithValue("@id", newkunde.ID);
                        command.Parameters.AddWithValue("@Preis", item.AktuellerPizzaPreis);
                        command.Parameters.AddWithValue("@ID_Speisen", item.ID);
                        command.Parameters.AddWithValue("@ID_Bestellung", id_Bestellung);

                        MySqlTransaction myTrans = null;

                        try
                        {
                            connection.Open();

                            // Start a local transaction
                            myTrans = connection.BeginTransaction();
                            // Must assign both transaction object and connection
                            // to Command object for a pending local transaction
                            command.Connection = connection;
                            command.Transaction = myTrans;

                            command.CommandText = @"INSERT INTO `kundenspeise` (`ID_Kundenspeise`, `Preis`, `ID_Speisen`, `ID_Bestellung`) 
                                                VALUES (NULL, @Preis, @ID_Speisen, @ID_Bestellung);";

                            command.ExecuteNonQuery();

                            //
                            // get the Last Inserted ID and to pass it to the next method which need it
                            command.CommandText = "SELECT LAST_INSERT_ID()";

                            using (MySqlDataReader reader = command.ExecuteReader())
                            {

                                if (reader != null && reader.Read())
                                    id_KundenSpeise = reader.GetInt64(0);
                            }

                            foreach (var extrazutat in item.ExtraZutaten)
                            {
                                // TODO: Remove doesn't work, dont't know why :o fuck this
                                if (command.Parameters.Contains("@ID_Kundenspeise"))
                                    command.Parameters.RemoveAt("@ID_Kundenspeise");

                                if (command.Parameters.Contains("@ID_Extrazutat"))
                                    command.Parameters.RemoveAt("@ID_Extrazutat");

                                command.Parameters.AddWithValue("@ID_Kundenspeise", id_KundenSpeise);
                                command.Parameters.AddWithValue("@ID_Extrazutat", extrazutat.ID);

                                command.CommandText = @"INSERT INTO `kundenspeisenextrazutat` (`ID_Kundenspeise`, `ID_EXTRAZUTAT`) 
                                                   VALUES (@ID_Kundenspeise, @ID_Extrazutat);";

                                command.ExecuteNonQuery();
                            }

                            myTrans.Commit();
                        }
                        catch (Exception ex)
                        {
                            // exception
                            RaiseExceptionEvent("Could not Create Customer Food, Trying Rollback");

                            try
                            {
                                myTrans.Rollback();
                            }
                            catch (MySqlException exx)
                            {
                                if (myTrans.Connection != null)
                                {
                                    RaiseExceptionEvent("An exception of type " + exx.GetType() +
                                                       " was encountered while attempting to roll back the transaction.");
                                }
                            }
                            return -1;
                        }
                    }
                }
            }
            return id_Bestellung;
        }

        /// <summary>
        /// TODO
        /// </summary>
        private void generateBestellungModel()
        {
            ObservableCollection<Bestellung> bestellungen = new ObservableCollection<Bestellung>();

            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM bestellungen";

                    try
                    {
                        connection.Open();

                        Bestellung bestellung;

                        long idKunde;

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                bestellung = new Bestellung
                                {
                                    ID_Bestellung = reader.GetInt32(0),
                                    Date = reader.GetDateTime(1),
                                    Lieferpauschale = reader.GetDecimal(2),
                                    Summe = reader.GetDecimal(3),
                                    Status = (Bestellung.BestellStatus)reader.GetInt16(4)
                                };

                                idKunde = reader.GetInt32(5);

                                bestellung.Kunde = m_Kunden.FirstOrDefault(k => k.ID == idKunde);

                                bestellungen.Add(bestellung);
                            }
                        }


                        m_Bestellungen = bestellungen;
                    }
                    //we should give an error to the GUI
                    catch (Exception ex)
                    {
                        RaiseExceptionEvent(ex.Message);
                        throw;
                    }
                }
            }

            generateBestellungenSpeisenModel();
        }
        /// <summary>
        /// Bestellungen-Speisen Modell erstellen
        /// </summary>
        private void generateBestellungenSpeisenModel()
        {
            List<KundenSpeisenExtraZutat> kundenExtraSpeisenZutaten = generateKundenSpeisenExtraZutaten();

            List<Speise> kundenSpeisen = new List<Speise>();

            int idSpeise;
            Speise speise;
            int idBestellung;

            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM kundenspeise";

                    try
                    {
                        connection.Open();

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                speise = null;

                                idSpeise = reader.GetInt32(2);
                                idBestellung = reader.GetInt32(3);

                                speise = new Speise(m_Speisen.FirstOrDefault(s => s.ID == idSpeise));

                                speise.ID = reader.GetInt32(0);
                                speise.GesamtPreis = reader.GetDecimal(1);

                                foreach (var item in kundenExtraSpeisenZutaten)
                                {
                                    if (item.IDKundenSpeise == speise.ID)
                                    {
                                        if (speise.ExtraZutaten == null)
                                            speise.ExtraZutaten = new ObservableCollection<ExtraZutat>();

                                        speise.ExtraZutaten.Add(item.KundenExtraZutat);
                                    }
                                }

                                foreach (var item in m_Bestellungen)
                                {
                                    if (item.ID_Bestellung == idBestellung)
                                    {
                                        if (item.Speisen == null)
                                            item.Speisen = new ObservableCollection<Speise>();

                                        item.Speisen.Add(speise);
                                    }
                                }
                            }
                        }
                    }
                    //we should give an error to the GUI
                    catch (Exception ex)
                    {
                        RaiseExceptionEvent(ex.Message);
                        throw;
                    }
                }
            }
        }

        private List<KundenSpeisenExtraZutat> generateKundenSpeisenExtraZutaten()
        {
            List<KundenSpeisenExtraZutat> extraZutaten = new List<KundenSpeisenExtraZutat>();

            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM kundenspeisenextrazutat";

                    try
                    {
                        connection.Open();

                        ExtraZutat extraZutat;

                        int IdExtraZutat;
                        int IdKundenSpeise;

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                extraZutat = null;

                                IdKundenSpeise = reader.GetInt32(0);
                                IdExtraZutat = reader.GetInt32(1);

                                extraZutaten.Add(new KundenSpeisenExtraZutat(IdKundenSpeise, m_ExtraZutaten.FirstOrDefault(e => e.ID == IdExtraZutat)));

                                //extraZutaten.Add(IdKundenSpeise, extraZutat);
                            }
                        }


                    }
                    //we should give an error to the GUI
                    catch (Exception ex)
                    {
                        RaiseExceptionEvent(ex.Message);
                        throw;
                    }
                }
            }

            return extraZutaten;
        }
        /// <summary>
        /// Speise erstellens
        /// </summary>
        /// <param name="food"></param>
        /// <returns></returns>
        public int CreateSpeise(Speise food)
        {
            int idFood = -1;
            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {

                    command.Parameters.AddWithValue("@Name", food.Name);
                    command.Parameters.AddWithValue("@PreisKlein", food.Preis_klein);
                    command.Parameters.AddWithValue("@PreisGross", food.Preis_groß);
                    command.Parameters.AddWithValue("@Typ", food.Typ);

                    MySqlTransaction myTrans = null;


                    try
                    {
                        connection.Open();

                        // Start a local transaction
                        myTrans = connection.BeginTransaction();
                        // Must assign both transaction object and connection
                        // to Command object for a pending local transaction
                        command.Connection = connection;
                        command.Transaction = myTrans;

                        command.CommandText = @"INSERT INTO `speisen` (`ID_Speisen`, `Typ`, `Name`, `Preis_klein`, `Preis_gross`) 
                VALUES (NULL, @Typ, @Name, @PreisKlein, @PreisGross);";

                        command.ExecuteNonQuery();

                        //
                        // get the Last Inserted ID and to pass it to the next method which need it
                        command.CommandText = "SELECT LAST_INSERT_ID()";

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {

                            if (reader != null && reader.Read())
                                idFood = reader.GetInt32(0);
                        }

                        command.Parameters.AddWithValue("@SpeisenID", idFood);

                        foreach (var zutat in food.Zutaten)
                        {
                            if (command.Parameters.Contains("@ZutatID"))
                                command.Parameters.RemoveAt("@ZutatID");

                            command.Parameters.AddWithValue("@ZutatID", zutat.ID);

                            command.CommandText = @"INSERT INTO `speisen_zutaten` (`ID_Speisen`, `ID_Zutaten`) 
                                                    VALUES (@SpeisenID, @ZutatID);";

                            command.ExecuteNonQuery();
                        }

                        myTrans.Commit();
                    }
                    catch (Exception)
                    {
                        // exception
                        RaiseExceptionEvent("Could not Create Food, Trying Rollback");

                        try
                        {
                            myTrans.Rollback();
                        }
                        catch (MySqlException exx)
                        {
                            if (myTrans.Connection != null)
                            {
                                RaiseExceptionEvent("An exception of type " + exx.GetType() +
                                                   " was encountered while attempting to roll back the transaction.");
                            }
                        }

                        return -1;
                    }
                }
            }

            return idFood;
        }
        /// <summary>
        /// Status Bestellung
        /// </summary>
        /// <param name="OpenedOrderViewModel"></param>
        /// <param name="newState"></param>
        internal void SaveOrderState(Bestellung OpenedOrderViewModel, int newState)
        {
            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {

                    command.Parameters.AddWithValue("@id", OpenedOrderViewModel.ID_Bestellung);
                    command.Parameters.AddWithValue("@State", newState);

                    MySqlTransaction myTrans = null;

                    try
                    {
                        connection.Open();

                        // Start a local transaction
                        myTrans = connection.BeginTransaction();
                        // Must assign both transaction object and connection
                        // to Command object for a pending local transaction
                        command.Connection = connection;
                        command.Transaction = myTrans;

                        command.CommandText = "UPDATE bestellungen SET Status=@State WHERE ID_BESTELLUNG = @id";

                        command.ExecuteNonQuery();

                        myTrans.Commit();
                    }
                    catch (Exception)
                    {
                        // exception
                        RaiseExceptionEvent("Could not Update Order, Trying Rollback");

                        try
                        {
                            myTrans.Rollback();
                        }
                        catch (MySqlException exx)
                        {
                            if (myTrans.Connection != null)
                            {
                                RaiseExceptionEvent("An exception of type " + exx.GetType() +
                                                   " was encountered while attempting to roll back the transaction.");
                            }
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Speise löschen
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        internal bool DeleteFood(int id)
        {
            using (MySqlConnection connection = new MySqlConnection(m_ConnectionString))
            {
                using (MySqlCommand command = connection.CreateCommand())
                {

                    command.Parameters.AddWithValue("@id", id);

                    MySqlTransaction myTrans = null;


                    try
                    {
                        connection.Open();

                        // Start a local transaction
                        myTrans = connection.BeginTransaction();
                        // Must assign both transaction object and connection
                        // to Command object for a pending local transaction
                        command.Connection = connection;
                        command.Transaction = myTrans;

                        command.CommandText = "DELETE from speisen WHERE ID_SPEISEN = @id";

                        command.ExecuteNonQuery();

                        command.CommandText = "DELETE from speisen_zutaten WHERE ID_SPEISEN = @id";

                        command.ExecuteNonQuery();

                        myTrans.Commit();
                    }
                    catch (Exception)
                    {
                        // exception
                        RaiseExceptionEvent("Could not Delete Food, Trying Rollback");

                        try
                        {
                            myTrans.Rollback();
                        }
                        catch (MySqlException exx)
                        {
                            if (myTrans.Connection != null)
                            {
                                RaiseExceptionEvent("An exception of type " + exx.GetType() +
                                                   " was encountered while attempting to roll back the transaction.");
                            }
                        }

                        return false;
                    }
                }
            }

            return true;
        }
    }
}